
# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2017/9/22 0022 下午 11:47
# @Author  : toby
# @Site    :
# @File    : mydb.py
# @Software: PyCharm

import pymysql
from pymysql.constants import CLIENT
import json
from jsonpath import jsonpath



class MySqlDb:
    # 连接数据库
    def __init__(self):

        try:
            with open('/root/django32_celery/c_apps/config.json', 'r', encoding='utf8')as fp:
                json_data = fp.read()
                json_data = json.loads(json_data)
                self.host = jsonpath(json_data, "$.testsss_db.host")
                self.port = jsonpath(json_data, "$.testsss_db.port")
                self.user = jsonpath(json_data, "$.testsss_db.user")
                self.passwd = jsonpath(json_data, "$.testsss_db.passwd")
                self.db = jsonpath(json_data, "$.testsss_db.db")
            # 打开数据库连接
            self.conn = pymysql.connect(host=self.host[0],
                                        port=self.port[0],
                                        user=self.user[0],
                                        passwd=self.passwd[0],
                                        db=self.db[0],
                                        charset='utf8',
                                        client_flag=CLIENT.MULTI_STATEMENTS)  # 添加client_flag参数)
            # 获取游标
            self.cursor = self.conn.cursor()

        except Exception as e:
            print(" 数据库连接失败 :{}".format(e))

    # sql语句执行,返回字典类型
    def select_sql(self, sql_string):
        self._query = sql_string
        print('查询语句:{}'.format(self._query))
        try:
            self.cursor.execute(query=self._query)
            coloumns = [row[0] for row in self.cursor.description]
            result = [[str(item) for item in row] for row in self.cursor.fetchall()]
            return [dict(zip(coloumns, row)) for row in result]
        except:
            self.conn.rollback()
            print('执行查询的失败 ')
            return None

    # 返回tuple类型  # 可以用来执行update等语句
    def sql_run(self, sql_string):
        self.query = sql_string
        print("执行SQL语句:{}".format(self.query))

        try:
            # 执行 ,提交 ,返回执行结果
            self.cursor.execute(query=self.query)
            self.conn.commit()  # 需要使用commint()才能操作提交成功
        except Exception as e:
            self.conn.rollback()
            print('执行sql失败:', e)
            return None

    # 断开数据库
    def close(self):
        self.cursor.close()
        self.conn.close()
        print('mysql closed')

    def dictfetchall(self,cursor):
        """
        Return all rows from a cursor as a dict.   返回一个字典
        Assume the column names are unique.
        """
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

if __name__ == '__main__':
    mydb = MySqlDb()

    sql = 'select * from t_charge_pile where mngId="4ebdcda13dba" limit 1;'
    ress = mydb.select_sql(sql)
    print(ress[0])
    print(ress[0]['boxCode'])

    sql = 'SELECT * FROM t_charge_user WHERE mobile ="13189593801";  '
    res = mydb.select_sql(sql)
    print(res[0]['userId'])

    sql = 'SELECT * FROM t_charge_deal WHERE userId =' + res[0]['userId'] + '  ORDER BY dealId DESC limit 1;'
    res = mydb.select_sql(sql)
    print(res[0]['dealId'])

    mydb.close()
